using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
namespace QLHXNK.DataAccess
{
    class ImportNoteData
    {
        #region---------------------------------------------Thêm, xoá, sửa-----------------------------------------

        public static int CreateImportNote(String importNoteCode, DateTime importNoteDate, double exchangeRate, String remark, int type)
        {
            try
            {
                string sqlstring = "insert into Import_Note(import_note_code,import_note_date,exchange_rate,remark,type) values('" + importNoteCode.Replace("'", "''") + "','"
                                    + importNoteDate.ToShortDateString() + "'," + exchangeRate + ",'" + remark.Replace("'", "''") + "'," + type + ")";
                ConnectionData connectionData = new ConnectionData();
                if (connectionData.Command(sqlstring) != 0)
                    return Int32.Parse(GetImportNote(importNoteCode,-1).Rows[0]["ID"].ToString());
                return 0;
            }
            catch
            {
                return 0;
            }
        }


        public static int DeleteImportNote(int importNoteID)
        {
            try
            {
                string sqlstring = "delete from Import_Note where ID=" + importNoteID + "";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.Command(sqlstring);
            }
            catch
            {
                return 0;
            }
        }


        public static int UpdateImportNote(int importNoteID, String importNoteCode, DateTime importNoteDate, double exchangeRate, String remark)
        {
            try
            {
                string sqlstring = "update Import_Note set import_note_code='" + importNoteCode.Replace("'", "''") + "',import_note_date='" + importNoteDate.ToShortDateString()
                                    + "',exchange_rate=" + exchangeRate + ",remark='" + remark.Replace("'", "''") + "' where ID=" + importNoteID + "";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.Command(sqlstring);
            }
            catch
            {
                return 0;
            }
        }

        #endregion

        #region ------------------------------------------Lấy thông tin---------------------------------------------

        public static DataTable GetImportNote(String importNoteCode, int type)
        {
            try
            {
                string queryCondition = "";
                if (importNoteCode != "")
                    queryCondition += " import_note_code = '" + importNoteCode.Replace("'", "''") + "' ";
                if(type!=-1)
                    queryCondition += " type = " + type + " ";
                string sqlstring = "select * from Import_Note ";
                if (queryCondition != "")
                    sqlstring += " where " + queryCondition;
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }


        /// <summary>
        /// Lấy ra đơn đặt hàng sau cùng trong tháng importNote
        /// </summary>
        /// <returns></returns>
        public static DataTable GetOrderOnPresentMonth(DateTime orderDate)
        {
            try
            {
                int intendDate = 31;
                String month = orderDate.Month.ToString();
                if (month.Length == 1)
                    month = "0" + month;
                String strStartDate=""+month+"/"+"01/"+orderDate.Year.ToString();
                String strEndDate=""+month+"/"+intendDate+"/"+orderDate.Year.ToString();
                DateTime startDate = DateTime.Parse(strStartDate);
                DateTime endDate;
                try
                {
                    endDate = DateTime.Parse(strEndDate);
                }
                catch 
                {
                    try
                    {
                        intendDate--;
                        strEndDate = "" + month + "/" + intendDate + "/" + orderDate.Year.ToString();
                        endDate = DateTime.Parse(strEndDate);
                    }
                    catch 
                    {
                        try
                        {
                            intendDate--;
                            strEndDate = "" + month + "/" + intendDate + "/" + orderDate.Year.ToString();
                            endDate = DateTime.Parse(strEndDate);
                        }
                        catch 
                        {
                            intendDate--;
                            strEndDate = "" + month + "/" + intendDate + "/" + orderDate.Year.ToString();
                            endDate = DateTime.Parse(strEndDate);
                        }
                    }
                }
                String strOrderDay = "DH/" + month + orderDate.Year.ToString();
                String sqlstring = "select * from `Import_Note` where import_note_date between #" + startDate + "# and #" + endDate + "#  order by import_note_code asc";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch 
            {
                return null;
            }
        }

        #endregion
    }
}
